﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace ChicagoDealsN
{
    public class DataUser
    {
        #region private data
        private int _user_Id;
        private string _user_Name=string.Empty;
        private string _user_Address = string.Empty;
        private string _email_Id = string.Empty;
        private string _password = string.Empty;
        private string _gender=string.Empty;
        private string _credit_Card_Number;
        private int _is_Active;
        private int _is_Vendor;
        private int _is_Admin;
        private int _balance;
        private string _state;

        public int User_Id
        {
            get { return _user_Id; }
            set { _user_Id = value;}
        }
        public string User_Name
        {
            get { return _user_Name; }
            set { _user_Name = value;}
        }
        public string User_Address
        {
            get { return _user_Address; }
            set { _user_Address = value;}
        }
        public string Email_Id
        {
            get { return _email_Id; }
            set { _email_Id = value;}
        }
        public string Password
        {
            get { return _password; }         
            set { _password = value;}
        }
        public string Gender
        {
            get { return _gender; }
            set { _gender = value;}
        }
        public string Credit_Card_Number
        {
            get { return _credit_Card_Number; }
            set { _credit_Card_Number = value;}
        }
        public int Is_Active
        {
            get { return _is_Active; }
            set { _is_Active = value;}
        }
        public int Is_Vendor
        {
            get { return _is_Vendor; }
            set { _is_Vendor = value;}
        }
        public int Is_Admin
        {
            get { return _is_Admin; }   
            set { _is_Admin = value;}
        }

        public DataUser() {}

        public DataUser(string userName)
        {
            User_Name = userName;
        }

        public int Balance
        {
            get { return _balance; }
            set { _balance = value; }
        }

        public string State
        {
            get { return _state; }
            set { _state = value; }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// 

        //private string strConnect = WebConfigurationManager.ConnectionStrings["cdmcoursedb.cstcis.cti.depaul.edu"].ConnectionString;
        //private string strConnect = WebConfigurationManager.ConnectionStrings["localhost"].ConnectionString;
        private string strConnect = "Data Source=localhost\\sqlexpress;Initial Catalog=yvyas;Integrated Security=True";

        private string getConnection(){ return strConnect; }
         
         public bool IsExists(string Email)
        {
            SqlConnection conPubs;
            //SqlCommand cmdExists;
            string Conn = getConnection();
            conPubs = new SqlConnection(Conn);
            conPubs.Open();

            SqlCommand cmdExists = new SqlCommand("SELECT Email_Id from Users WHERE Email_Id=@Email", conPubs);
            cmdExists.Parameters.AddWithValue("@Email", Email);
            Object x = cmdExists.ExecuteScalar();
            if(x!=null)
                return true; 
            else 
                return false;

            
        }

         public bool IsExists(string Email, string Username)
         {
             SqlConnection conPubs;
             //SqlCommand cmdExists;
             string Conn = getConnection();
             conPubs = new SqlConnection(Conn);
             conPubs.Open();

             SqlCommand cmdExists = new SqlCommand("SELECT Email_Id from Users WHERE Email_Id=@Email AND User_Name=@UserName", conPubs);
             cmdExists.Parameters.AddWithValue("@Email", Email);
             cmdExists.Parameters.AddWithValue("@User_Name", Username);
             Object x = cmdExists.ExecuteScalar();
             if (x != null)
                 return true;
             else
                 return false;


         }

        /*
        public Boolean DL_create_account(string username,string pass,string name,string email,string cc_payment,string gender) 
        {
           return true;
        }
        */
        public DataSet DLGetUserById(int id)
        {
            SqlConnection conPubs;
            SqlCommand cmdSelect;

            string conn = getConnection();
            conPubs = new SqlConnection(conn);
            conPubs.Open();
            cmdSelect = new SqlCommand("SELECT * FROM Users WHERE User_Id=" + id, conPubs);

            DataSet set = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(cmdSelect.CommandText, conPubs);
            da.Fill(set, "ITEMBYID");

            conPubs.Close();
            return set;

        }



        public Boolean DLCreateAccount(string _username, string _password, string _address, string _email, string _cc_Payment, char _gender, int _balance, string _state, int _is_Vendor)
        {
            SqlConnection conPubs;
            SqlCommand cmdCreate;
            string Conn = getConnection();
            conPubs = new SqlConnection(Conn);
            try
            {
                conPubs.Open();
                Boolean flag = IsExists(_email, _user_Name);
                if (flag)
                { 
                    return false;
                }
                else
                {  
                 /* Removing as Id is auto generated : Yash
                   cmdUserId = new SqlCommand("SELECT Max(User_Id) from USER",conPubs);
                  _user_Id = cmdUserId.ExecuteReader();
                  _user_Id = _user_Id + 1;
                  */
                  _is_Active =1;
                  //_is_Vendor =0;
                  _is_Admin =0;

                //cmd.Connection = connection;
                cmdCreate = new SqlCommand("INSERT INTO Users (User_Name, User_Address, Email_Id, Password, Gender, Credit_Card_Number, Is_Active, Is_Vendor, Is_Admin, Balance, State) VALUES (@User_Name_PARAM, @User_Address_PARAM, @Email_Id_PARAM, @Password_PARAM, @Gender_PARAM, @Credit_Card_Number_PARAM, @Is_Active_PARAM, @Is_Vendor_PARAM, @Is_Admin_PARAM, @Bal, @State)", conPubs);
                //cmd.Parameters.AddWithValue("@User_Id_PARAM", _user_Id);
                cmdCreate.Parameters.AddWithValue("@User_Name_PARAM", _username);
                cmdCreate.Parameters.AddWithValue("@User_Address_PARAM", _address);
                cmdCreate.Parameters.AddWithValue("@Email_Id_PARAM", _email);
                cmdCreate.Parameters.AddWithValue("@Password_PARAM", _password);
                cmdCreate.Parameters.AddWithValue("@Gender_PARAM", _gender);
                cmdCreate.Parameters.AddWithValue("@Credit_Card_Number_PARAM", _cc_Payment);
                cmdCreate.Parameters.AddWithValue("@Is_Active_PARAM", _is_Active);
                cmdCreate.Parameters.AddWithValue("@Is_Vendor_PARAM", _is_Vendor);
                cmdCreate.Parameters.AddWithValue("@Is_Admin_PARAM", _is_Admin);
                cmdCreate.Parameters.AddWithValue("@Bal", _balance);
                cmdCreate.Parameters.AddWithValue("@State", _state);
                cmdCreate.ExecuteNonQuery();                
            }
            }
            catch(Exception e)
            {
                throw e;
            }
            finally
            {
                conPubs.Close();
            }

            return true;

        }    //test

        public void Load()
        {
            Load(User_Name);
        }
        public void Load(string _userName)
        {
                
            SqlConnection conPubs;
            SqlCommand cmdload=new SqlCommand();
            string Conn = getConnection();
            conPubs = new SqlConnection(Conn);
            
            conPubs.Open();
            cmdload.CommandText = "SELECT * FROM Users WHERE User_Name=@User_Name_PARAM";
            cmdload.Parameters.AddWithValue("@User_Name_PARAM", _userName);
            cmdload.Connection = conPubs;   
            System.Data.SqlClient.SqlDataReader reader = cmdload.ExecuteReader();

            if (true == reader.Read() && reader != null)
            {
                _user_Id = reader.GetInt32(9);
                _user_Name = reader.GetString(0);
                _user_Address = reader.GetString(1);
                _email_Id = reader.GetString(2);
                _password = reader.GetString(3);
                _gender = reader.GetString(4);
                _credit_Card_Number = reader.GetString(10);
                _is_Active = reader.GetInt32(5);
                _is_Vendor = reader.GetInt32(6);
                _is_Admin = reader.GetInt32(7);
                _balance = reader.GetInt32(8);
                _state = reader.GetString(11);
            }

            reader.Close();

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        
        public Boolean DLUpdateAccount(int _user_Id,string _password, string _username,string _address, string _email,string _cc_Payment,char _gender)
        //BLUpdateAccount(UserId, txtPassword.Text, txtName.Text, txtAddress.Text, txtEmail.Text, txtCc1.Text, Convert.ToChar(Gender))
        {
            
            SqlConnection conPubs;
            SqlCommand cmdUpdate=new SqlCommand();
            string Conn = getConnection();
            conPubs = new SqlConnection(Conn);
            try
            {
                conPubs.Open();
             
                cmdUpdate = new SqlCommand ("UPDATE Users SET User_Name=@User_Name_PARAMS, User_Address=@User_Address_PARAMS, Email_Id=@Email_Id_PARAMS, Password=@Password_PARAMS, Gender=@Gender_PARAMS, Credit_Card_Number=@Credit_Card_Number_PARAMS WHERE User_Id=@User_Id_PARAM",conPubs);
                cmdUpdate.Parameters.AddWithValue("@User_Id_PARAM", _user_Id);
                cmdUpdate.Parameters.AddWithValue("@User_Name_PARAMS", _username);
                cmdUpdate.Parameters.AddWithValue("@User_Address_PARAMS", _address);
                cmdUpdate.Parameters.AddWithValue("@Email_Id_PARAMS", _email);
                cmdUpdate.Parameters.AddWithValue("@Password_PARAMS", _password);
                cmdUpdate.Parameters.AddWithValue("@Gender_PARAMS", _gender);
                cmdUpdate.Parameters.AddWithValue("@Credit_Card_Number_PARAMS", _cc_Payment);
               /* cmdUpdate.Parameters.AddWithValue("@Is_Active_PARAM", _is_Active);
                cmdUpdate.Parameters.AddWithValue("@Is_Vendor_PARAM", _is_Vendor);
                cmdUpdate.Parameters.AddWithValue("@Is_Admin_PARAM", _is_Admin);*/

                cmdUpdate.ExecuteNonQuery();

            }
            catch (Exception e) {
                throw e;
            }
             finally
            {
                conPubs.Close();
            }
             return true;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        private Boolean DLDelete(int _user_Id)
        {

            SqlConnection conPubs;
            SqlCommand cmdDelete = new SqlCommand();
            string Conn = getConnection();
            conPubs = new SqlConnection(Conn);
            try
            {
                conPubs.Open();
               
                cmdDelete =new SqlCommand( "DELETE FROM User WHERE User_Id=@User_Id_PARAM",conPubs);
                cmdDelete.Parameters.AddWithValue("@User_Id_PARAM", _user_Id);

                cmdDelete.ExecuteNonQuery();
            }
            catch
            {
                return false;
            }
            finally
            {
                conPubs.Close();
            }

            return true;
        }
        #endregion
    }
}

